package vip.idiy.anwei_android_iot.utils;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * ClassName DatabaseUtil
 * describe: 创建sqlite数据库
 * Created: 勇
 * Email:118085890@qq.com
 * Version: 1.0
 * Create in 9:58 2023/4/17
 */
public class DatabaseUtil {
    private static final String DATABASE_NAME = "my_database";
    private static final int DATABASE_VERSION = 1;
    private static DatabaseUtil instance;
    private SQLiteDatabase database;

    private DatabaseUtil(Context context) {
        database = new DatabaseHelper(context).getWritableDatabase();
    }

    public static synchronized DatabaseUtil getInstance(Context context) {
        if (instance == null) {
            instance = new DatabaseUtil(context);
        }
        return instance;
    }


    /**
     * 增加
     *
     * @param table
     * @param values
     */
    public long insert(String table, ContentValues values) {
        long result = database.insert(table, null, values);
        return result;
    }

    /**
     * 更新
     *
     * @param table
     * @param values
     * @param whereClause
     * @param whereArgs
     */
    public int update(String table, ContentValues values, String whereClause, String[] whereArgs) {
        int result = database.update(table, values, whereClause, whereArgs);
        return result;
    }

    /**
     * 删除
     *
     * @param table
     * @param whereClause
     * @param whereArgs
     */
    public int delete(String table, String whereClause, String[] whereArgs) {
        int result = database.delete(table, whereClause, whereArgs);
        return result;
    }

    /**
     * 查询
     *
     * @param table
     * @param columns
     * @param selection
     * @param selectionArgs
     * @param groupBy
     * @param having
     * @param orderBy
     * @return
     */
    public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy) {
        Cursor cursor = database.query(table, columns, selection, selectionArgs, groupBy, having, orderBy);
        return cursor;
    }

    /**
     * 查询
     *
     * @param table
     * @param columns
     * @param selection
     * @param selectionArgs
     * @param groupBy
     * @param having
     * @param orderBy
     * @param limit
     * @return
     */
    public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) {
        Cursor cursor = database.query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
        return cursor;
    }

    /**
     * 执行一条SQL查询语句,返回查询结果
     *
     * @param sql
     * @param selectionArgs 查询参数
     * @return
     */
    public Cursor rawQuery(String sql, String[] selectionArgs) {
        Cursor cursor = database.rawQuery(sql, selectionArgs);
        return cursor;
    }

    /**
     * 关闭数据库
     */
    public void colorDB(){
        if (database != null) {
            database.close();
        }
    }

    private static class DatabaseHelper extends SQLiteOpenHelper {
        // 设备信息表
        // info_id,device_id,type_id,scene_id,firmware_version,on_line,rssi,device_data
        // 信息ID,设备ID,类型ID,场景ID,固件版本,是否在线,信号强度,设备数据
        private static final String CREATE_TABLE_DEVICE_INFO_SQL =
                "CREATE TABLE IF NOT EXISTS device_info (" +
                        "info_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
                        "device_id VARCHAR(128) NOT NULL," +
                        "type_id INTEGER NOT NULL," +
                        "scene_id INTEGER NOT NULL," +
                        "firmware_version VARCHAR(64)," +
                        "on_line INTEGER," +
                        "rssi INTEGER," +
                        "device_data VARCHAR(2048)" +
                        ")";

        // 设备类型表
        // type_id,type_name,type_img,parent_id,parent_name
        // 类型ID,类型名称,类型图片,父类型ID,父类型名称
        private static final String CREATE_TABLE_DEVICE_TYPE_SQL =
                "CREATE TABLE IF NOT EXISTS device_type (" +
                        "type_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
                        "type_name VARCHAR(128) NOT NULL," +
                        "type_img VARCHAR(255) NOT NULL," +
                        "parent_id INTEGER DEFAULT 0," +
                        "parent_name VARCHAR(128)" +
                        ")";

        // 场景表
        // scene_id,scene_name,scene_icon
        // 场景ID,场景名称,场景图标
        private static final String CREATE_TABLE_DEVICE_SCENE_SQL =
                "CREATE TABLE IF NOT EXISTS device_scene (" +
                        "scene_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
                        "scene_name VARCHAR(128) NOT NULL," +
                        "scene_icon VARCHAR(255) NOT NULL" +
                        ")";

        // 设备表
        // id,设备ID,WIFI账号,WIFI密码,是否自动连接WIFI,MQTT服务器,MQTT端口,MQTT账号,MQTT密码,MQTT订阅主题,MQTT发送主题

        private static final String CREATE_TABLE_DEVICE_DATA_SQL =
                "CREATE TABLE IF NOT EXISTS device_data (" +
                        "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
                        "device_id VARCHAR(64) NOT NULL," +
                        "wifi_name VARCHAR(32) NOT NULL," +
                        "wifi_psw VARCHAR(64) NOT NULL," +
                        "auto_wifi INTEGER," +
                        "mqtt_server VARCHAR(128) NOT NULL," +
                        "mqtt_port INTEGER," +
                        "mqtt_name VARCHAR(32) NOT NULL," +
                        "mqtt_psw VARCHAR(64) NOT NULL," +
                        "mqtt_sub VARCHAR(255) NOT NULL," +
                        "mqtt_pub VARCHAR(255) NOT NULL" +
                        ")";

        private static final String[] CREATE_TABLES = {CREATE_TABLE_DEVICE_INFO_SQL, CREATE_TABLE_DEVICE_TYPE_SQL, CREATE_TABLE_DEVICE_SCENE_SQL, CREATE_TABLE_DEVICE_DATA_SQL};
        // 添加默认的设备类型
        private static final String DEFAULT_DEVICE_TYPE = "INSERT INTO device_type (type_name,type_img,parent_id,parent_name) VALUES ('默认类型','照明开关',0,NULL)";
        // 添加默认的设备位置
        private static final String DEFAULT_DEVICE_SCENE = "INSERT INTO device_scene (scene_name,scene_icon) VALUES ('默认位置','卧室')";

        private DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            for (String createTable : CREATE_TABLES) {
                db.execSQL(createTable);
            }
            // 添加默认数据
            db.execSQL(DEFAULT_DEVICE_TYPE);
            db.execSQL(DEFAULT_DEVICE_SCENE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            for (String createTable : CREATE_TABLES) {
                db.execSQL("DROP TABLE IF EXISTS " + createTable);
            }
        }
    }
}


